package com.itheima.dao;

import com.itheima.domian.Student;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    // 查询所有
    public List findAll(){
        Connection con = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        ArrayList<Student> list = new ArrayList<>();
        try {
            // 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 获取数据库连接
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db4", "root", "123");
            // 查询的sql语句
            String sql = "select * from student";
            // 获取执行对象执行sql语句
            stat = con.prepareStatement(sql);
            // 获取结果集
            rs = stat.executeQuery();
            // 处理结果集
            while (rs.next()){
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");

                // 封装对象
                Student stu = new Student(sid,name,age,birthday);
                /*stu.setSid(sid);
                stu.setName(name);
                stu.setAge(age);
                stu.setBirthday(birthday);*/
                list.add(stu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    // 删除
    public int delete(String sid){
        Connection con = null;
        PreparedStatement stat = null;
        int i = 0;
        try {
            // 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 连接数据库
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db4","root","123");
            // 删除的sql语句
            String sql = "delete from student where sid=?";
            // 获取执行者对象
            stat = con.prepareStatement(sql);
            stat.setInt(1, Integer.parseInt(sid));
            i = stat.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return i;
    }

    // 添加
    public void insert(Student stu){
        Connection con = null;
        PreparedStatement stat = null;
        try {
            // 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 获取数据库连接
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db4","root","123");
            // sql语句
            String sql = "insert into student values (null,?,?,?)";
            stat = con.prepareStatement(sql);
            stat.setString(1,stu.getName());
            stat.setInt(2,stu.getAge());
            String birthday = new SimpleDateFormat("yyyy-MM-dd").format(stu.getBirthday());
            stat.setDate(3, Date.valueOf(birthday));
            stat.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
